package com.wanmait.shoppingsys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.shoppingsys.util.DbHelper;
import com.wanmait.shoppingsys.util.ManagePager;
import com.wanmait.shoppingsys.vo.Evaluation;
import com.wanmait.shoppingsys.vo.Specialty;
import com.wanmait.shoppingsys.vo.UserInfo;

public class EvaluationDAO {
	private static EvaluationDAO evaluationDAO;

	private EvaluationDAO() {
	}

	public static EvaluationDAO getDAO() {
		if (evaluationDAO == null) {
			evaluationDAO = new EvaluationDAO();
		}
		return evaluationDAO;
	}
	public List<Evaluation> findBySql(String sql,List params) {
		ArrayList<Evaluation> evaluations = new ArrayList<Evaluation>();
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			for(int i=0;params!=null&&i<params.size();i++) {
				ps.setObject(i+1, params.get(i));
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				Evaluation evaluation = new Evaluation();
				evaluation.setId(rs.getInt("id"));
				evaluation.setContent(rs.getString("content"));
				evaluation.setSpecialtyId(rs.getInt("specialty_id"));
				evaluation.setUserId(rs.getInt("user_id"));
				evaluation.setTime(rs.getTimestamp("time"));
            
				
				  UserInfo userinfo = new UserInfo();
				  userinfo.setGender(rs.getString("gender"));
				
				  
				  Specialty specialty=new Specialty();
				  specialty.setId(rs.getInt("specialty_id"));
				  specialty.setName(rs.getString("name"));
				  
				  evaluation.setUesrinfo(userinfo);
				  evaluation.setSpecialty( specialty);
				 

				evaluations.add(evaluation);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbHelper.close(rs, ps, conn);
		}

		return evaluations;
	}
	

	public void insert(Evaluation evaluation) {

		String sql = "insert into evaluation(user_id,specialty_id,content) VALUES(?,?,?)";
		ArrayList params = new ArrayList();
		params.add(evaluation.getUserId());
		
		params.add(evaluation.getSpecialtyId());
		params.add(evaluation.getContent());
		DbHelper.update(sql, params);
	}

	public List<Evaluation> findBySpecialtyId(Integer specialtyId) {
		ArrayList<Evaluation> evaluations = new ArrayList<Evaluation>();


		String sql = "select evaluation.*,specialty.name AS specialty_name," + 
				"userinfo.name,userinfo.email,userinfo.gender " + 
				"from evaluation " + 
				"left join userinfo on evaluation.user_id=userinfo.id " + 
				"LEFT JOIN specialty on evaluation.specialty_id=specialty.id " + 
				"WHERE evaluation.specialty_id=? ";

		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, specialtyId);
			rs = ps.executeQuery();
			while (rs.next()) {
				Evaluation evaluation = new Evaluation();
				evaluation.setId(rs.getInt("id"));
				evaluation.setContent(rs.getString("content"));
				evaluation.setSpecialtyId(rs.getInt("specialty_id"));
				evaluation.setUserId(rs.getInt("user_id"));
				evaluation.setTime(rs.getTimestamp("time"));
            
				
				  UserInfo userinfo = new UserInfo();

				  //userinfo.setAddress(rs.getString("address"));

				  userinfo.setGender(rs.getString("gender"));
				  userinfo.setName(rs.getString("name"));

				 // userinfo.setPhone(rs.getString("phone"));
				  //userinfo.setPic(rs.getString("pic"));
				  

				

				  Specialty specialty=new Specialty();
				  specialty.setId(rs.getInt("specialty_id"));
				  specialty.setName(rs.getString("name"));
				  
				  evaluation.setUesrinfo(userinfo);
				  evaluation.setSpecialty( specialty);
				 

				evaluations.add(evaluation);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbHelper.close(rs, ps, conn);
		}

		return evaluations;
	}
	
	public void delete(Integer id) {
		String sql = "DELETE FROM  evaluation WHERE id=?";
		ArrayList params = new ArrayList();
		params.add(id);
		DbHelper.update(sql,params);
	}

	
	public  List<Evaluation> findAll() {
		String sql="SELECT evaluation.*,type.kind AS type_kind FROM evaluation LEFT JOIN type ON specialty.type_id=type.id";
		return this.findBySql(sql, null);
	}


	
}
